package Project;

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.Point;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;

public class DAOTest extends JFrame implements MouseListener, ActionListener {
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:orcl";
	String userid = "user01";
	String passwd = "oracle";
	Connection con = null;
	Statement stmt = null;;
	PreparedStatement pstmt = null;

	int row, column, row1, column1;
	boolean addstate = false, delstate = false;
	JFrame f = new JFrame();
	JTable jtable;
	String[] colNames = { "과목번호", "과목명", "학점" };
	DefaultTableModel model;
	DefaultTableModel submodel;
	JButton selbtn = new JButton("선택");
	JButton delbtn = new JButton("삭제");
	JButton savebtn = new JButton("저장");
	JButton subaddbtn = new JButton("과목추가");
	JButton subdelbtn = new JButton("과목삭제");
	JButton subsavebtn = new JButton("과목저장");
	JPanel jp = new JPanel();
	JPanel subjp = new JPanel();
	JTable southTable = new JTable();
	String selsub, selsub1;

	JPanel northjp = new JPanel();
	JComboBox jb = new JComboBox();
	JTextField searchTF = new JTextField(10);
	JButton searchbtn = new JButton("검색");

	DAOTest() {
		
		super("TEST");
	}
	public void job(){
		setLayout(new BorderLayout());
		// this.setLocation(300, 200);
		// this.setPreferredSize(new Dimension(400, 200));

		jp.setLayout(new GridLayout(4, 1));
		model = new DefaultTableModel(null, colNames) {
			// 테이블 수정 불가능
			public boolean isCellEditable(int i, int c) {
				return false;
			}
		};

		submodel = new DefaultTableModel(null, colNames) {
			// 테이블 수정 불가능
			public boolean isCellEditable(int i, int c) {
				return false;
			}
		};

		// 콤보박스
		jb.addItem("전체보기");
		jb.addItem("과목검색");
		jb.addItem("교수검색");

		
		jtable = new JTable(model);
		southTable = new JTable(submodel);
		jtable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
		JScrollPane spane = new JScrollPane(jtable);
		JScrollPane spane1 = new JScrollPane(southTable);

		// sel.setSize(125, 125);
		selbtn.setMaximumSize(new Dimension(10, 10));
		// sel.setPreferredSize(new Dimension(40, 40));
		subjp.add(selbtn);
		subjp.add(delbtn);
		subjp.add(savebtn);
		subjp.add(subaddbtn);
		subjp.add(subdelbtn);
		subjp.add(subsavebtn);
		northjp.add(this.jb);
		northjp.add(this.searchTF);
		northjp.add(this.searchbtn);

		jp.add(northjp);
		jp.add(spane);
		jp.add(subjp);
		jp.add(spane1);
		// this.getContentPane().add(new JLabel("  "), "Center");

		add(jp, "Center");
		// add(sel, "South");
		this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setSize(500, 350);
		// this.pack();

		this.setVisible(true);
		Point a = this.getLocation();
		System.out.println(a.getX());
		System.out.println(a.getY());

		subsavebtn.addActionListener(this);
		jtable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
		// 마우스 리스너
		jtable.addMouseListener(this);
		southTable.addMouseListener(this);
		savebtn.addActionListener(this);
		// 과목 추가
		selbtn.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				String str = jtable.getValueAt(row, column).toString();
				for (int i = 0; i < submodel.getRowCount(); i++) {
					try {
						if (selsub.equals(southTable.getValueAt(i, 0))) {
							System.out.println("if문 수행");
							return;
						}
					} catch (Exception x) {
						return;
					}
				}
				if (addstate == true) {
					Vector temp = new Vector();
					temp.add(jtable.getValueAt(row, 0).toString());
					temp.add(jtable.getValueAt(row, 1).toString());
					temp.add(jtable.getValueAt(row, 2).toString());
					submodel.addRow(temp);
				}
				System.out.println(submodel.getRowCount());
			}
		});
		
		// 과목추가
		subaddbtn.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				Vector temp = new Vector();
				temp.add(jtable.getValueAt(row, 0).toString());
				temp.add(jtable.getValueAt(row, 1).toString());
				temp.add(jtable.getValueAt(row, 2).toString());
				submodel.addRow(temp);
				model.removeRow(row);
			}
		});
		// 과목 삭제---------------------------------------
		delbtn.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				System.out.println("삭제버튼");
				if (delstate == true) {
					submodel.removeRow(row1);
				}
				delstate = false;
			}
		});
		subdelbtn.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				Vector temp = new Vector();
				temp.add(southTable.getValueAt(row, 0).toString());
				temp.add(southTable.getValueAt(row, 1).toString());
				temp.add(southTable.getValueAt(row, 2).toString());
				submodel.removeRow(row1);
				model.addRow(temp);
			}
		});
		this.searchbtn.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				if (jb.getSelectedItem().equals("전체보기")) {
					String sql = "select * from subject";
					subAdd(sql);
				}
				if (jb.getSelectedItem().equals("과목검색")) {
					System.out.println("과목검색");
					String str = searchTF.getText();
					//String sql = "select * from subject where name="+str;
					String sql = "select * from subject where regexp_like(name,'"+str+"','i')";
					subAdd(sql);
				}
				if (jb.getSelectedItem().equals("교수검색")) {

				}
			}
		});
	}
	
	// 과목추가(학생)
	public void subAdd(String query) {
		OpenDB();
		int count = model.getRowCount();
		for(int i=0; i<count; i++)
		{
			model.removeRow(0);
		}
		ResultSet rs;
		try {
			System.out.println(model.getRowCount());
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				Vector vec = new Vector();
				vec.add(rs.getInt("num"));
				vec.add(rs.getString("name"));
				vec.add(rs.getString("grade"));
				model.addRow(vec);
			}
			System.out.println(model.getRowCount());
			//model.removeRow(0);
			 
		} catch (Exception e) {

		}
	}

	// 전체과목 불러오기
	public ArrayList<subjectDTO> subList() {
		ArrayList<subjectDTO> list = new ArrayList<subjectDTO>();
		OpenDB();
		String query = "select num, name, grade from subject";
		ResultSet rs;
		try {
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				System.out.println("while수행");
				subjectDTO temp = new subjectDTO();
				temp.setNum(rs.getInt("num"));
				temp.setName(rs.getString("name"));
				temp.setGrade(rs.getString("grade"));
				list.add(temp);
			}
			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i).name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public void mouseClicked(MouseEvent me) {
		if (me.getSource() == southTable) {
			row1 = southTable.getSelectedRow();
			column1 = southTable.getSelectedColumn();
			selsub1 = southTable.getValueAt(row1, 0).toString();
			System.out.println(selsub1);
			delstate = true;
		} else {
			row = jtable.getSelectedRow();
			column = jtable.getSelectedColumn();
			selsub = jtable.getValueAt(row, 0).toString();
			System.out.println(jtable.getValueAt(row, 0));
			System.out.println(selsub);
			addstate = true;
		}
	}

	public void mouseEntered(MouseEvent e) {
	}

	public void mouseExited(MouseEvent e) {
	}

	public void mousePressed(MouseEvent e) {
	}

	public void mouseReleased(MouseEvent e) {
	}

	public void OpenDB() {
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, userid, passwd);
			stmt = con.createStatement();
			System.out.println("연결완료");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 로그인
	public boolean Login(String m_name, String m_num, boolean state) {
		// DB 연결 설정
		OpenDB();
		String query="", pro_num = "", name = "";
		ResultSet rs;
		
		// 교수일 경우와 교수일경우 sql문이 다름
		if(state == true)
			query = "select stu_num, stu_name from student";
		else
			query = "select pro_num, pro_name from professor";
		try {
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				Integer temp_num;
				// 교수, 학생일 경우를 나누어 
				// 학번및 교수번호와 이름을 받아옴
				if(state == true){
					pro_num = rs.getString("stu_num");
					name = rs.getString("stu_name");
				}
				else{
					pro_num = rs.getString("pro_num");
					name = rs.getString("pro_name");
				}
				// 학번및 교수번호와 이름이 일치할경우 true 리턴
				if (m_num.equals(pro_num) && m_name.equals(name)) {
					System.out.println("true");
					return true;
				}
			}
		} catch (Exception e) {e.printStackTrace();}
		// 일치하지 않을경우 false 리턴
		finally {
			try {
				stmt.close(); con.close();
			} catch (SQLException e) {e.printStackTrace();}
		}
		return false;
	}

	// 회원가입
	public void Join(String dept, String year, 
			String stunum, String name, boolean state) {
		// DB 연결 설정
		OpenDB();
		String sql="";
		// 교수일 경우와 교수일경우 sql문이 다름
		if(state == false)
			sql = "insert into professor(pro_num, pro_name, pro_dept)"
				+ "values(?, ?, ?)";
		else
			sql = "insert into student(stu_num, stu_name, stu_dept, stu_year)"
					+ "values(?,?,?,?)";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, stunum);
			pstmt.setString(2, name);
			pstmt.setString(3, dept);
			// 학생일경우 학년도 추가
			if(state == true)
				pstmt.setString(4, year);
			int n = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void actionPerformed(ActionEvent e) {
		OpenDB();
		// 수강신청 저장(학생)
		if (e.getSource() == savebtn) {
			String sql = "insert into regist(stu_num, sub_num, grade)"
					+ "values(?,?,?)";
			try {
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, 100);
				pstmt.setInt(2, 1000);
				pstmt.setInt(3, 80);
				int n = pstmt.executeUpdate();
			} catch (Exception x) {
				x.printStackTrace();
			}
		}
		// 과목추가 저장(교수)
		if (e.getSource() == subsavebtn) {
			String sql = "update subject set pro_num=? where num=?";
			try {
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, 90);
				pstmt.setInt(2, 100);
				int n = pstmt.executeUpdate();
			} catch (Exception x) {
				x.printStackTrace();
			}
		}
	}

	// 성적확인(학생)
	public void gradeSel() {
		OpenDB();
		String sql = "select * from regist";
		ResultSet rs;
		try {
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Vector vec = new Vector();
				vec.add(rs.getInt("stu_num"));
				vec.add(rs.getInt("sub_num"));
				vec.add(rs.getString("grade"));
				model.addRow(vec);
			}
		} catch (Exception e) {

		}
	}
	// 지도 학생 조회
	public void stuSel(String num) {
		OpenDB();
		String sql = "select * from student where stu_pronum = '"+num+"'";
		ResultSet rs;
		try {
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Vector vec = new Vector();
				vec.add(rs.getInt("stu_num"));
				vec.add(rs.getInt("sub_num"));
				vec.add(rs.getString("grade"));
				model.addRow(vec);
			}
		} catch (Exception e) {

		}
	}

	// 과목등록(교수)
	public void subRegist() {
		OpenDB();

		String sql = "select * from subject where pro_num is null";
		ResultSet rs;
		try {
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Vector vec = new Vector();
				vec.add(rs.getInt("num"));
				vec.add(rs.getString("name"));
				vec.add(rs.getString("grade"));
				/*
				 * subjectDTO dto = new subjectDTO();
				 * dto.setNum(rs.getInt("num"));
				 * dto.setName(rs.getString("name"));
				 * dto.setGrade(rs.getString("grade"));
				 */
				model.addRow(vec);
			}
		} catch (Exception e) {

		}
	}

	public static void main(String[] args) {
		DAOTest dao = new DAOTest();
		// dao.subList();
		// dao.subAdd();
		// dao.subRegist();
		dao.job();
	}

}
